<?php

namespace App\Console\Commands;

use App\Models\DailyReport;
use App\Models\Users;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use function GuzzleHttp\Psr7\str;

class DailyTotal extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'daily-total';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = '每日统计';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {

        $today = date('Y-m-d');
        /** @var DailyReport $daily */
        $daily = DailyReport::firstOrCreate(['date' => $today]);

        // $grid->column('active_ct', __('活跃人数'));
        $daily->active_ct = Users::where('last_active_time', '>=', strtotime($today))->count();

        // $grid->column('classify_ct', __('选择分类继续人数'));
        $daily->classify_ct = Users::where('classify_date', '=', strtotime($today))->count();

        //  `register_ct` int(11) NOT NULL DEFAULT '1' COMMENT '注册人数',
        $daily->register_ct = Users::where('created_at', '>=', $today)->count();

        //  $grid->column('un_book_ct', __('当日注册未选择书的人数'));
        $selectedBookCt = DB::table(DB::raw('(select * from user_book_chapter where user_id in (select id from users where created_at > ?)) as a'))
            ->setBindings([$today])
            ->count();
        $daily->un_book_ct = $daily->register_ct - $selectedBookCt;


        //  `mobile_ct` int(11) NOT NULL DEFAULT '1' COMMENT '手机号授权人数',
        $daily->mobile_ct = Users::whereBetween('mobile_time', [strtotime($today), strtotime($today . '+1 day')])->count();

        $subSql = '(select user_id from user_subscribe where date = ? and type = ? and status = ? group by user_id) as a';
        // `type` int(11) NOT NULL DEFAULT '0' COMMENT '类型：0、首页 1、10单词 2、20单词 3、30单词',
        //  `status` int(11) NOT NULL DEFAULT '0' COMMENT '订阅状态：0、弹窗 1、点击  2、订阅成功',


        //   `fir_sub_click` int(11) NOT NULL DEFAULT '1' COMMENT '首页订阅按钮点击人数',
        //  `fir_sub_sub` int(11) NOT NULL DEFAULT '1' COMMENT '订阅人数',
        $daily->fir_sub_click = DB::table(DB::raw($subSql))->setBindings([$today, 0, 1])->count();
        $daily->fir_sub_sub = DB::table(DB::raw($subSql))->setBindings([$today, 0, 2])->count();

        //  `one_sub_pop` int(11) NOT NULL DEFAULT '1' COMMENT '订阅弹窗-10词触发人数',
        //  `one_sub_click` int(11) NOT NULL DEFAULT '1' COMMENT '点击人数',
        //  `one_sub_sub` int(11) NOT NULL DEFAULT '1' COMMENT '订阅人数',
        $daily->one_sub_pop = DB::table(DB::raw($subSql))->setBindings([$today, 1, 0])->count();
        $daily->one_sub_click = DB::table(DB::raw($subSql))->setBindings([$today, 1, 1])->count();
        $daily->one_sub_sub = DB::table(DB::raw($subSql))->setBindings([$today, 1, 2])->count();

        //  `two_sub_pop` int(11) NOT NULL DEFAULT '1' COMMENT '订阅弹窗-20词触发人数',
        //  `tow_sub_click` int(11) NOT NULL DEFAULT '1' COMMENT '点击人数',
        //  `two_sub_sub` int(11) NOT NULL DEFAULT '1' COMMENT '订阅人数',
        $daily->two_sub_pop = DB::table(DB::raw($subSql))->setBindings([$today, 2, 0])->count();
        $daily->tow_sub_click = DB::table(DB::raw($subSql))->setBindings([$today, 2, 1])->count();
        $daily->two_sub_sub = DB::table(DB::raw($subSql))->setBindings([$today, 2, 2])->count();

        //  `thr_sub_pop` int(11) NOT NULL DEFAULT '1' COMMENT '订阅弹窗-30词触发人数',
        //  `thr_sub_click` int(11) NOT NULL DEFAULT '1' COMMENT '点击人数',
        //  `thr_sub_sub` int(11) NOT NULL DEFAULT '1' COMMENT '订阅人数',
        $daily->thr_sub_pop = DB::table(DB::raw($subSql))->setBindings([$today, 3, 0])->count();
        $daily->thr_sub_click = DB::table(DB::raw($subSql))->setBindings([$today, 3, 1])->count();
        $daily->thr_sub_sub = DB::table(DB::raw($subSql))->setBindings([$today, 3, 2])->count();


        $daily->save();
    }
}
